CS185c Spring 2010Practice Final

To study for the final I would suggest you: (1) Know how to do (by heart) all the practice problems. (2) Go over your notes at least three times. Second and third time try to see how much you can remember from the first time. (3) Go over the homework problems. (4) Try to create your own problems similar to the ones I have given and solve them. (5) Skim the relevant sections from the book. (6) If you want to study in groups, at this point you are ready to quiz each other. The practice final is below. Here are some facts about the actual final: (a) It is comprehensive (b) It is closed book, closed notes. Nothing will be permitted on your desk except your pen (pencil) and test. (c) You should bring photo ID. (d) There will be more than one version of the test. Each version will be of comparable difficulty. (e) It is 10 problems, 6 problems will be on material since the lecture before the midterm, four problems will come from the topics covered prior to the midterm. (f) Two problems will be exactly (less typos) off of the practice final, and one will be off of practice midterm.

1. Given an example where a left outer join would yield a different result than an inner join. What is the difference between a correlated subquery and an uncorrelated subquery?

2. What are the differences between user defined functions and stored procedures? What is the difference between a checkpoint and a savepoint?

3. Give an example showing that sections in a DB2 package might be executed out of sequence when an application is run.

4. Briefly describe each of the following four types of dynamic SQL statements: PREPARE, DESCRIBE, EXECUTE, and EXECUTE IMMEDIATE.

5. With regard to DB2 security, how are primary and secondary authorization IDs established? What is an exit routine?

6. Which SQL commands are authorized using composite privileges which make use of the CURRENT SQLID? How can multi-level security be implemented using DB2?

7. Briefly describe the following kind of concurrency anomalies: Phantom Row Anomaly. Non-repeatable Read. Dirty Read Anomaly. Lost Update Anomaly. What isolation levels are available in DB2? What anomalies can occur at each of these levels?

8. What is a latch? What is claim and drain? (unrelated) What are compensation records?

9. How are undo and redo lists built using a checkpoint? How is the BSDS used in the recovery process?

10. Briefly describe the DB2 start-up sequence.